package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;



import db.common.DBHelper;
import model.common.Employee;

public class EmployeeDao {
	
	public void addEmployee(Employee e) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_employee " + "(employeeName,employeeAge,employeeSex,"
				    + " employeeEmail,employeeAddress,employeeIdentifyId,employeePassword,employeeTelephone) "
				    + " values(?,?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, e.getEmployeeName());
		ptmt.setString(2, e.getEmployeeAge());
		ptmt.setString(3, e.getEmployeeSex());
		ptmt.setString(4, e.getEmployeeEmail());
		ptmt.setString(5, e.getEmployeeAddress());
		ptmt.setString(6, e.getEmployeeIdentifyId());
		ptmt.setString(7, e.getEmployeePassword());
		ptmt.setString(8, e.getEmployeeTelephone());

		ptmt.execute();
	}

	public void deleteEmployee(Integer employeeId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_employee  where employeeId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, employeeId);
		ptmt.execute();
	}
	
	public void updateEmployee(Employee e) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_employee set employeeName=?,employeeAge=?,employeeSex=?,employeeEmail=?,"
		+ " employeeAddress=?,employeeIdentifyId=?,employeePassword=?,employeeTelephone=?"
				+ " where employeeId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, e.getEmployeeName());
		ptmt.setString(2, e.getEmployeeAge());
		ptmt.setString(3, e.getEmployeeSex());
		ptmt.setString(4, e.getEmployeeEmail());
		ptmt.setString(5, e.getEmployeeAddress());
		ptmt.setString(6, e.getEmployeeIdentifyId());
		ptmt.setString(7, e.getEmployeePassword());
		ptmt.setString(8, e.getEmployeeTelephone());
		ptmt.setInt(9, e.getEmployeeId());
		
		ptmt.execute();
	}

	public Employee get(Integer employeeId) throws SQLException {
		Employee e = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_employee where employeeId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, employeeId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			e = new Employee();
			e.setEmployeeId(rs.getInt("employeeId"));
			e.setEmployeeName(rs.getString("employeeName"));
			e.setEmployeeAge(rs.getString("employeeAge"));
			e.setEmployeeSex(rs.getString("EmployeeSex"));
			e.setEmployeeEmail(rs.getString("EmployeeEmail"));
			e.setEmployeeAddress(rs.getString("employeeAddress"));
			e.setEmployeeIdentifyId(rs.getString("EmployeeIdentifyId"));
			e.setEmployeePassword(rs.getString("EmployeePassword"));
			e.setEmployeeTelephone(rs.getString("EmployeeTelephone"));
		}
		return e;
	}
	
	public List<Employee> query(List<Map<String, Object>> params) throws SQLException{
		List<Employee> result=new ArrayList<Employee>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_employee where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Employee e = null;
		while (rs.next()) {
		e=new Employee();
			e.setEmployeeId(rs.getInt("employeeId"));
			e.setEmployeeName(rs.getString("employeeName"));
			e.setEmployeeAge(rs.getString("employeeAge"));
			e.setEmployeeSex(rs.getString("EmployeeSex"));
			e.setEmployeeEmail(rs.getString("EmployeeEmail"));
			e.setEmployeeAddress(rs.getString("employeeAddress"));
			e.setEmployeeIdentifyId(rs.getString("EmployeeIdentifyId"));
			e.setEmployeePassword(rs.getString("EmployeePassword"));
			e.setEmployeeTelephone(rs.getString("EmployeeTelephone"));
			result.add(e);
		}
		
		return result;
		}
		
	public List<Employee> query() throws Exception{
		List<Employee> result=new ArrayList<Employee>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select employeeId,employeeName,employeeAge from tab_employee  ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		Employee e=null;
		while(rs.next()){
			e=new Employee();
			e.setEmployeeId(rs.getInt("employeeId"));
			e.setEmployeeName(rs.getString("employeeName"));
			e.setEmployeeAge(rs.getString("employeeAge"));
			result.add(e);
		}
		
		return result;
		
	}
	
	public List<Employee> query(String employeeName,String employeePassword) throws Exception{
		List<Employee> result=new ArrayList<Employee>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_employee  ");
		
		sb.append(" where employeeName like ? and employeePassword like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+employeeName+"%");
		ptmt.setString(2, "%"+employeePassword+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Employee e=null;
		while(rs.next()){
			e=new Employee();
			e.setEmployeeId(rs.getInt("employeeId"));
			e.setEmployeeName(rs.getString("employeeName"));
			e.setEmployeeAge(rs.getString("employeeAge"));
			e.setEmployeeSex(rs.getString("employeeSex"));
			e.setEmployeeEmail(rs.getString("employeeEmail"));
			e.setEmployeeAddress(rs.getString("employeeAddress"));
			e.setEmployeeIdentifyId(rs.getString("employeeIdentifyId"));
			e.setEmployeePassword(rs.getString("employeePassword"));
			e.setEmployeeTelephone(rs.getString("employeeTelephone"));
			result.add(e);
		}
		return result;
	}
	public Employee getLoginEmployeeName(String employeeName) {
		Connection conn = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConnection();
			String sql = "select * from tab_employee where EmployeeName=?";
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, employeeName);
			rs = ptmt.executeQuery();
			Employee e = new Employee();
			if (rs.next()) {
				e.setEmployeeId(rs.getInt("employeeId"));
				e.setEmployeeAge(rs.getString("employeeAge"));
				e.setEmployeeSex(rs.getString("employeeSex"));
				e.setEmployeeEmail(rs.getString("employeeEmail"));
				e.setEmployeeAddress(rs.getString("employeeAddress"));
				e.setEmployeeIdentifyId(rs.getString("employeeIdentifyId"));
				e.setEmployeePassword(rs.getString("employeePassword"));
				e.setEmployeeTelephone(rs.getString("employeeTelephone"));
				return e;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			DBHelper.close(rs);
		}
	}

	

}
